﻿<%@ WebHandler Language="C#" Class="ZSTouLiaoInfoReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;

/**
 * Description: 装配试车厂投料统计报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.11.25
 **/

public class ZSTouLiaoInfoReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDetail":
                        r = getDetail(jObject);//查询明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //查询方法
    Result getData(JObject jObject)
    {
        string sql = @"
SELECT t.mfgordername,t.attributevalue engineno,t.productfamilyname,COUNT(t.productbatchno) flCount,SUM(t.qty) flQty,SUM(t.ptCount) ptCount,SUM(t.ptQty) ptQty
FROM
(
    SELECT cm.mfgordername,u.attributevalue,pf.productfamilyname,pb2.productname partproductname,p2.description partproductdesc,
            wfs.workflowstepname,cm.productname, jsm.t_wlms description,cm.productbatchno,cm.qty,
            decode(im.ischange,'0','已配套','1','已取消',im.ischange) status,
            decode(im.ischange,NULL,'0','1') ptCount,
            CASE WHEN im.ischange IS NOT NULL THEN cm.qty
                ELSE 0 END ptQty,
            im.containerno,im.ischange,decode(pm.parttype,'0','大件','1','小件') producttype
    FROM collecterpmaterial cm
    LEFT JOIN issueerpmaterial im ON im.collecterpmaterialid=cm.collecterpmaterialid --配套记录
    LEFT JOIN mfgorder mfg ON mfg.mfgordername = cm.mfgordername
    LEFT JOIN Userattribute u ON u.parentid=mfg.mfgorderid AND u.userattributename='发动机'
    LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
    LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
    LEFT JOIN workflowproductfamilylist wpf ON wpf.workflowid = p.workflowid
    LEFT JOIN productfamily pf ON pf.productfamilyid = wpf.productfamilylistid
    LEFT JOIN jbst_sync_mes jsm ON jsm.t_code = cm.productname
    LEFT JOIN enginepartplan ep ON ep.containerid = im.containerid
    LEFT JOIN partmenuinfo pm ON pm.partmenuinfoid = im.partmenuinfoid
    LEFT JOIN product p2 ON p2.productid = ep.productid
    LEFT JOIN productbase pb2 ON pb2.productbaseid = p2.productbaseid
    LEFT JOIN workflowstep wfs ON wfs.workflowstepid = ep.workflowstepid
    LEFT JOIN factory f ON f.factoryid = mfg.factoryid
    WHERE mfg.mfgordername IS NOT NULL AND u.attributevalue IS NOT NULL AND f.factoryname = '装配试车厂'
";
        //发动机号
        if (jObject["engineno"] != null && jObject["engineno"].ToString() != "")
        {
            sql += string.Format(" AND u.attributevalue = '{0}'", jObject["engineno"].ToString());
        }

        //订单号
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND mfg.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        //部件号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND pb2.productname = '{0}'", jObject["productname"].ToString());
        }

        //机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND pf.productfamilyname LIKE '%{0}%'", jObject["productfamilyname"].ToString());
        }

        //阶段
        if (jObject["workflowstepname"] != null && jObject["workflowstepname"].ToString() != "")
        {
            sql += string.Format(" AND wfs.workflowstepname LIKE '%{0}%'", jObject["workflowstepname"].ToString());
        }

        //零件类型
        if (jObject["producttype"] != null && jObject["producttype"].ToString() != "")
        {
            string producttype = jObject["producttype"].ToString();
            if (producttype == "大件")
            {
                sql += " AND pm.parttype = '0'";
            }
            if (producttype == "小件")
            {
                sql += " AND pm.parttype = '1'";
            }
        }

        //配套状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            string status = jObject["status"].ToString();
            if (status == "已配套")
            {
                sql += " AND im.ischange = '0'";
            }
            if (status == "已取消")
            {
                sql += " AND im.ischange = '1'";
            }
            if (status == "未配套")
            {
                sql += " AND im.ischange is null";
            }
        }

        sql += @") t GROUP BY t.mfgordername,t.attributevalue,t.productfamilyname";

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询明细
    Result getDetail(JObject jObject)
    {
        string sql = @"
SELECT cm.mfgordername,u.attributevalue,pf.productfamilyname,pb2.productname partproductname,p2.description partproductdesc,
        wfs.workflowstepname,cm.productname,jsm.t_wlms description,cm.productbatchno,cm.qty,
        decode(im.ischange,'0','已配套','1','已取消',null,'未配套') status,
        decode(im.ischange,NULL,'0','1') ptCount,
        CASE WHEN im.ischange IS NOT NULL THEN cm.qty
            ELSE 0 END ptQty,
        im.containerno,im.ischange,decode(pm.parttype,'0','大件','1','小件') producttype
FROM collecterpmaterial cm
LEFT JOIN issueerpmaterial im ON im.collecterpmaterialid=cm.collecterpmaterialid --配套记录
LEFT JOIN mfgorder mfg ON mfg.mfgordername = cm.mfgordername
LEFT JOIN Userattribute u ON u.parentid=mfg.mfgorderid AND u.userattributename='发动机'
LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
LEFT JOIN workflowproductfamilylist wpf ON wpf.workflowid = p.workflowid
LEFT JOIN productfamily pf ON pf.productfamilyid = wpf.productfamilylistid
LEFT JOIN jbst_sync_mes jsm ON jsm.t_code = cm.productname
LEFT JOIN enginepartplan ep ON ep.containerid = im.containerid
LEFT JOIN partmenuinfo pm ON pm.partmenuinfoid = im.partmenuinfoid
LEFT JOIN product p2 ON p2.productid = ep.productid
LEFT JOIN productbase pb2 ON pb2.productbaseid = p2.productbaseid   
LEFT JOIN workflowstep wfs ON wfs.workflowstepid = ep.workflowstepid
LEFT JOIN factory f ON f.factoryid = mfg.factoryid
WHERE mfg.mfgordername IS NOT NULL AND u.attributevalue IS NOT NULL AND f.factoryname = '装配试车厂'
";
        //发动机号
        if (jObject["engineno"] != null && jObject["engineno"].ToString() != "")
        {
            sql += string.Format(" AND u.attributevalue = '{0}'", jObject["engineno"].ToString());
        }

        //订单号
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND mfg.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        //部件号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND pb2.productname = '{0}'", jObject["productname"].ToString());
        }

        //机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND pf.productfamilyname LIKE '%{0}%'", jObject["productfamilyname"].ToString());
        }

        //阶段
        if (jObject["workflowstepname"] != null && jObject["workflowstepname"].ToString() != "")
        {
            sql += string.Format(" AND wfs.workflowstepname LIKE '%{0}%'", jObject["workflowstepname"].ToString());
        }

        //零件类型
        if (jObject["producttype"] != null && jObject["producttype"].ToString() != "")
        {
            string producttype = jObject["producttype"].ToString();
            if (producttype == "大件")
            {
                sql += " AND pm.parttype = '0'";
            }
            if (producttype == "小件")
            {
                sql += " AND pm.parttype = '1'";
            }
        }

        //配套状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            string status = jObject["status"].ToString();
            if (status == "已配套")
            {
                sql += " AND im.ischange = '0'";
            }
            if (status == "已取消")
            {
                sql += " AND im.ischange = '1'";
            }
            if (status == "未配套")
            {
                sql += " AND im.ischange is null";
            }
        }

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = @"
SELECT cm.mfgordername,u.attributevalue,pf.productfamilyname,pb2.productname partproductname,p2.description partproductdesc,
        wfs.workflowstepname,cm.productname,jsm.t_wlms description,decode(pm.parttype,'0','大件','1','小件') producttype,cm.productbatchno,
        cm.qty,decode(im.ischange,'0','已配套','1','已取消',NULL,'未配套') status
FROM collecterpmaterial cm
LEFT JOIN issueerpmaterial im ON im.collecterpmaterialid=cm.collecterpmaterialid --配套记录
LEFT JOIN mfgorder mfg ON mfg.mfgordername = cm.mfgordername
LEFT JOIN Userattribute u ON u.parentid=mfg.mfgorderid AND u.userattributename='发动机'  
LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
LEFT JOIN workflowproductfamilylist wpf ON wpf.workflowid = p.workflowid
LEFT JOIN productfamily pf ON pf.productfamilyid = wpf.productfamilylistid 
LEFT JOIN jbst_sync_mes jsm ON jsm.t_code = cm.productname
LEFT JOIN enginepartplan ep ON ep.containerid = im.containerid
LEFT JOIN partmenuinfo pm ON pm.partmenuinfoid = im.partmenuinfoid
LEFT JOIN product p2 ON p2.productid = ep.productid
LEFT JOIN productbase pb2 ON pb2.productbaseid = p2.productbaseid  
LEFT JOIN workflowstep wfs ON wfs.workflowstepid = ep.workflowstepid
LEFT JOIN factory f ON f.factoryid = mfg.factoryid
WHERE mfg.mfgordername IS NOT NULL AND u.attributevalue IS NOT NULL AND f.factoryname = '装配试车厂'
";
        //发动机号
        if (jObject["engineno"] != null && jObject["engineno"].ToString() != "")
        {
            sql += string.Format(" AND u.attributevalue = '{0}'", jObject["engineno"].ToString());
        }

        //订单号
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND mfg.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        //部件号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND pb2.productname = '{0}'", jObject["productname"].ToString());
        }

        //机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND pf.productfamilyname LIKE '%{0}%'", jObject["productfamilyname"].ToString());
        }

        //阶段
        if (jObject["workflowstepname"] != null && jObject["workflowstepname"].ToString() != "")
        {
            sql += string.Format(" AND wfs.workflowstepname LIKE '%{0}%'", jObject["workflowstepname"].ToString());
        }

        //零件类型
        if (jObject["producttype"] != null && jObject["producttype"].ToString() != "")
        {
            string producttype = jObject["producttype"].ToString();
            if (producttype == "大件")
            {
                sql += " AND pm.parttype = '0'";
            }
            if (producttype == "小件")
            {
                sql += " AND pm.parttype = '1'";
            }
        }

        //配套状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            string status = jObject["status"].ToString();
            if (status == "已配套")
            {
                sql += " AND im.ischange = '0'";
            }
            if (status == "已取消")
            {
                sql += " AND im.ischange = '1'";
            }
            if (status == "未配套")
            {
                sql += " AND im.ischange is null";
            }
        }
        sql += " ORDER BY cm.mfgordername,u.attributevalue";
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dt.Columns["mfgordername"].Caption = "订单号";
        dt.Columns["attributevalue"].Caption = "发动机号";
        dt.Columns["productfamilyname"].Caption = "机型";
        dt.Columns["partproductname"].Caption = "部件号";
        dt.Columns["partproductdesc"].Caption = "部件名称";
        dt.Columns["workflowstepname"].Caption = "阶段";
        dt.Columns["productname"].Caption = "零件号";
        dt.Columns["description"].Caption = "零件名称";
        dt.Columns["productbatchno"].Caption = "批次";
        dt.Columns["producttype"].Caption = "零件类型";
        dt.Columns["qty"].Caption = "数量";
        dt.Columns["status"].Caption = "配套状态";
        ExportHelper.ExportExcel(dt, jObject["factoryname"].ToString() + "投料统计报表");
    }
}